import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import sqlalchemy
import bz2
import shutil

def unzip_bz2(bz2_file, output_file):
    with bz2.open(bz2_file, 'rb') as f_in:
        with open(output_file, 'wb') as f_out:
            shutil.copyfileobj(f_in, f_out)

# Read the fixed-width file, skipping the first row (column names row)
# Adjust widths based on your file's format
column_widths = [26]*47  # Add widths for each column
column_names = ['DATE', 'TIME', 'DAYS_SINCE_JAN', 'CAVITY_TEMP', 'CAVITY_PRESSURE',
       'DAS_TEMP_C', 'ETALON_TEMP', 'HEATER_CURRENT',
       'INLET_PROPORTIONAL_VALVE', 'OUTLET_PROPORTIONAL_VALVE',
       'SOLENOID_VALVES', 'MULTI_POS_VALVE', 'SPECTRUMID', 'CLOCK_DRIFT',
       'CO2', 'CO2_CORR', 'CH4', 'H2O', 'CO2_USER_AVE1', 'CO2_CORR_AVE1',
       'CH4_CORR_AVE1', 'CO2_H20_CORR', 'TIME_DELAY_CO2', 'TIME_DELAY_CH4',
       'CO2_GAL_PEAK', 'CO2_Y_PARAMETER', 'CO2_BASELINE', 'CM_ADJUST_C',
       'CO2_WLM_OFFSET', 'QUADSHIFT', 'FINECURRENT_MEAN_CO2',
       'LASER1_TEMP_OFFSET_CO2', 'CM_ADJUST_L2', 'CH4_WLM_OFFSET',
       'FINECURRENT_MEAN_CH4', 'LASER2_TEMP_OFFSET', 'CH4_Y', 'CH4_BASELINE',
       'PZT_CENTER', 'PZT_SD', 'STDV_RESIDUALS', 'PZT_FILTERED',
       'WLM_SETPOINT_FILTERED', 'BAD_RINGDOWNS_FILTERED', 'SPARSE_FILTERED',
       'TOTAL_FILTERED_CO2', 'TOTAL_FILTERED_CH4', 'E_TIME']

unzip_bz2('Z_CAWN_I_58448_20221016130000_O_GHG-FLD-CO2CH4-CRDS-S024.bz2', 'unzipped1301.txt')
df = pd.read_fwf('unzipped1301.txt', widths=column_widths, skiprows=[0], header=None)

# Merge the first two columns into a single string column

df['E_TIME'] = pd.to_datetime(df.iloc[:, 0].astype(str) + ' ' + df.iloc[:, 1].astype(str)+' +08:00', utc=True)

#, '%m/%d/%y %H:%M:%S.%f'
df.columns = column_names

# Convert the remaining columns to double
df.iloc[:, 2:-1] = df.iloc[:, 2:-1].astype(float)


# Define the MySQL connection string
engine = create_engine('mysql+mysqlconnector://grafana:grafana@localhost/grafanadb')

# Define the data types for the columns in the DataFrame
# Varchar column named 'merged_column', double columns named 'col1', 'col2', ...
dtype_mapping = {'E_TIME': sqlalchemy.DateTime, 'DATE': sqlalchemy.String(8), 'TIME': sqlalchemy.String(10)}
for i in range(2, len(df.columns)-1):
    dtype_mapping[df.columns[i]] = sqlalchemy.Double


# Write the DataFrame to the MySQL database
df.to_sql(name='rdt_1301', con=engine, if_exists='append', index=False, dtype=dtype_mapping)

# Close the database connection
engine.dispose()
